/**
 * 
 */
package org.compiere.model;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import extend.org.compiere.hrm.TaxVariable;
import extend.org.compiere.hrm.TypePayment;
import extend.org.compiere.utils.Util;

/**
 * @author V.Sokolov
 *
 */
public class Mhrmstatement extends X_hrm_statement {

	/**
	 * 
	 */
	private static final long serialVersionUID = 8176486868020741438L;
	/**	Static Logger	*/
	private static CLogger	s_log	= CLogger.getCLogger (Mhrmstatement.class);

	/**
	 * @param ctx
	 */
	public Mhrmstatement(Properties ctx) {
		super(ctx);
	}
	
	/**
	 * @param ctx
	 * @param hrm_statement_ID
	 * @param trxName
	 */
	public Mhrmstatement(Properties ctx, int hrm_statement_ID, String trxName) {
		super(ctx, hrm_statement_ID, trxName);
	}
	
	/**
	 * @param ctx
	 * @param rs
	 * @param trxName
	 */
	public Mhrmstatement(Properties ctx, ResultSet rs, String trxName) {
		super(ctx, rs, trxName);
	}
	
	// Error message
	private StringBuilder errmsg = new StringBuilder();

	@Override
	protected boolean afterSave(boolean newRecord, boolean success) {
		
		if(newRecord){
			if(gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_BONUS)){
				
				if(gethrm_bonus_ID() == 0)
					errmsg.append(Util.requiredField(COLUMNNAME_hrm_bonus_ID)).append("\n");
				
				Util.checkErrMsg(errmsg);
					
				Mhrmbonus bonus = new Mhrmbonus(getCtx(), gethrm_bonus_ID(), get_TrxName());
				MhrmbonusLine[] lines = bonus.getLinesBonusSort(bonus.gethrm_bonus_ID());
				
				int line = 10;
				for(MhrmbonusLine l: lines){
					BigDecimal amt = new BigDecimal(0);
					Mhrmstatementline stLine = new Mhrmstatementline(getCtx(), 0, get_TrxName());
					stLine.sethrm_statement_ID(get_ID());
					stLine.setC_BPartner_ID(l.getC_BPartner_ID());
					stLine.setLine(new BigDecimal(line));
					
					amt = l.gethrm_total();
					amt = amt.add(l.gethrm_IPN().negate());
					amt = amt.add(l.gethrm_OPV().negate());
					
					stLine.setAmount(amt);
					stLine.setAmountPaid(amt);
					stLine.saveEx();
					line += 10;
				}
			}
			else if(gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_BUSINESSTRIP)){
				
				if(gethrm_businesstrip_ID() == 0)
					errmsg.append(Util.requiredField(COLUMNNAME_hrm_businesstrip_ID)).append("\n");
				
				Util.checkErrMsg(errmsg);
					
				Mhrmbusinesstrip trip = new Mhrmbusinesstrip(getCtx(), gethrm_businesstrip_ID(), get_TrxName());
				Mhrmbusinesstripline[] lines = trip.getLinesTrip();
				
				int line = 10;
				for(Mhrmbusinesstripline l: lines){
					Mhrmstatementline stLine = new Mhrmstatementline(getCtx(), 0, get_TrxName());
					stLine.sethrm_statement_ID(get_ID());
					stLine.setC_BPartner_ID(l.getC_BPartner_ID());
					stLine.setLine(new BigDecimal(line));
					stLine.setAmount(l.gethrm_total());
					stLine.setAmountPaid(l.gethrm_total());
					stLine.saveEx();					
					line += 10;
				}
			}
			else if(gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_MATERIALAID)){
				
				if(gethrm_materialaid_ID() == 0)
					errmsg.append(Util.requiredField(COLUMNNAME_hrm_materialaid_ID)).append("\n");
				
				Util.checkErrMsg(errmsg);
					
				Mhrmmaterialaid aid = new Mhrmmaterialaid(getCtx(), gethrm_materialaid_ID(), get_TrxName());
				
				Mhrmstatementline stLine = new Mhrmstatementline(getCtx(), 0, get_TrxName());
				stLine.sethrm_statement_ID(get_ID());
				stLine.setC_BPartner_ID(aid.getC_BPartner_ID());
				stLine.setLine(new BigDecimal(10));
				stLine.setAmount(aid.gethrm_total());
				stLine.setAmountPaid(aid.gethrm_total());
				stLine.saveEx();
			}
			else if(gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_REVIEW)){
				
				if(gethrm_review_ID() == 0)
					errmsg.append(Util.requiredField(COLUMNNAME_hrm_review_ID)).append("\n");
				
				Util.checkErrMsg(errmsg);
					
				Mhrmreview review = new Mhrmreview(getCtx(), gethrm_review_ID(), get_TrxName());
				Mhrmreviewline[] lines = review.getLinesReview();
				
				int line = 10;
				for(Mhrmreviewline l: lines){
					Mhrmstatementline stLine = new Mhrmstatementline(getCtx(), 0, get_TrxName());
					stLine.sethrm_statement_ID(get_ID());
					stLine.setC_BPartner_ID(l.getC_BPartner_ID());
					stLine.setLine(new BigDecimal(line));
					stLine.setAmount(l.gethrm_total());
					stLine.setAmountPaid(l.gethrm_total());
					stLine.saveEx();					
					line += 10;
				}
			}
			else if(gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_VACATION)){
				
				if(gethrm_vacation_ID() == 0)
					errmsg.append(Util.requiredField(COLUMNNAME_hrm_vacation_ID)).append("\n");
				
				Util.checkErrMsg(errmsg);
					
				Mhrmvacation vacation = new Mhrmvacation(getCtx(), gethrm_vacation_ID(), get_TrxName());
				Mhrmvacationline[] lines = vacation.getLinesVacation();
				
				int line = 10;
				for(Mhrmvacationline l: lines){
					BigDecimal amt = new BigDecimal(0);
					Mhrmstatementline stLine = new Mhrmstatementline(getCtx(), 0, get_TrxName());
					stLine.sethrm_statement_ID(get_ID());
					stLine.setC_BPartner_ID(l.getC_BPartner_ID());
					stLine.setLine(new BigDecimal(line));
					
					TaxVariable taxIO = getTax("'"+TypePayment.VACATIONAID+"'", l.getC_BPartner_ID(), vacation.getDateFrom());
					
					amt = l.gethrm_total();
					amt = amt.add(l.gethrm_materialaid());
					amt = amt.add(l.gethrm_IPN().negate());
					amt = amt.add(l.gethrm_OPV().negate());
					amt = amt.add(new BigDecimal(taxIO.getIPN()).negate());
					amt = amt.add(new BigDecimal(taxIO.getOPV()).negate());
					stLine.setAmount(amt);
					stLine.setAmountPaid(amt);
					stLine.saveEx();					
					line += 10;
				}
			}
			else if(gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_COTCH)
					|| gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_CNALOG)
					|| gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_OPV)
					|| gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_IPN)
					|| gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_SALARY)){
				
				if(gethrm_payment_ID() == 0)
					errmsg.append(Util.requiredField(COLUMNNAME_hrm_payment_ID)).append("\n");
				
				Util.checkErrMsg(errmsg);
				
				Mhrmpayment pay = new Mhrmpayment(getCtx(), gethrm_payment_ID(), get_TrxName());
				Mhrmpaymentlist[] lines = pay.getLinesPaymentSort(pay.gethrm_payment_ID());
				int line = 10;
				for(Mhrmpaymentlist l: lines){
					Mhrmstatementline stLine = new Mhrmstatementline(getCtx(), 0, get_TrxName());
					
					stLine.sethrm_statement_ID(get_ID());
					stLine.setC_BPartner_ID(l.getC_BPartner_ID());					
					stLine.setLine(new BigDecimal(line));

					if (gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_OPV)){
						BigDecimal vOPV = Mhrmpayment.getSalSumColumn(l.getC_BPartner_ID(), l.gethrm_payment_list_ID(),"OPV");
						stLine.setAmount(vOPV);
						stLine.setAmountPaid(vOPV); 
					}else if(gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_IPN)){
						BigDecimal vIPN = Mhrmpayment.getSalSumColumn(l.getC_BPartner_ID(), l.gethrm_payment_list_ID(),"IPN");
						stLine.setAmount(vIPN);
						stLine.setAmountPaid(vIPN); 
					}else if(gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_COTCH)){
						BigDecimal vCOtch = Mhrmpayment.getSalSumColumn(l.getC_BPartner_ID(), l.gethrm_payment_list_ID(),"COTCH");
						stLine.setAmount(vCOtch);
						stLine.setAmountPaid(vCOtch); 
					}else if(gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_CNALOG)){
						BigDecimal vCNalog = Mhrmpayment.getSalSumColumn(l.getC_BPartner_ID(), l.gethrm_payment_list_ID(),"CNALOG");
						stLine.setAmount(vCNalog);
						stLine.setAmountPaid(vCNalog); 
					}else if(gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_SALARY)){
						//BigDecimal vSalary = Mhrmpayment.getSalSumColumn(l.getC_BPartner_ID(), l.gethrm_payment_list_ID(),"RESULT");
						
						SalarySum amount = getSalaryAmount(l.gethrm_payment_list_ID(), l.getC_BPartner_ID());
						BigDecimal vSalary = amount.getAmount();
						vSalary = vSalary.add(amount.getIllness());
						vSalary = vSalary.add(amount.getOvertime());
						vSalary = vSalary.add(amount.getMaid());
						vSalary = vSalary.add(amount.getInter().negate());
						vSalary = vSalary.add(amount.getCheckoff().negate());
						vSalary = vSalary.add(amount.getLoan().negate());
						
						String typeTax = "'"+TypePayment.PAYMENT+"','"+TypePayment.ILLNESS+"','"+TypePayment.BUSINESSTRIP+"'";
						
						TaxVariable taxIO = getTax(typeTax, l.getC_BPartner_ID(), pay.getDateFrom());

						//
						vSalary = vSalary.add(new BigDecimal(taxIO.getIPN()).negate());
						vSalary = vSalary.add(new BigDecimal(taxIO.getOPV()).negate());
						
						if(vSalary.signum() == -1)
							vSalary = new BigDecimal(0);
							
						
						stLine.setAmount(vSalary);
						stLine.setAmountPaid(vSalary);
					}				
					if(stLine.getAmount().doubleValue() != 0){
						stLine.saveEx();					
						line += 10;
					}
				}
			}
		}
		
		return true;
	}
	
	private TaxVariable getTax(String taxType, int BPartnerID, Timestamp dateFrom){
		
		TaxVariable result = new TaxVariable();
		
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
			
		// 
		String sql_ = "select * from hrm_tax \n" +
				"	where hrm_typetax in ("+taxType+") \n" +
				"	and c_bpartner_id="+BPartnerID+" and to_char(datefrom, 'YYYY-MM') = to_char("+DB.TO_DATE(dateFrom, true)+", 'YYYY-MM')";
			try
			{
				pstmt = DB.prepareStatement(sql_,null);				
				rs = pstmt.executeQuery();	
				double ipn = 0.;
				double opv = 0.;
				while(rs.next()){
					opv = opv + rs.getDouble("hrm_opv");
					ipn = ipn + rs.getDouble("hrm_ipn");
				}
				result.setIPN(ipn);
				result.setOPV(opv);
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}
		
		return result;
	}
	
	/**	Statement Lines	*/
	private Mhrmstatementline[] m_lines;
	
	/**
	 * 	Get HR Statement Lines
	 * 	@param whereClause starting with AND
	 * 	@return lines
	 */
	private Mhrmstatementline[] getLines (String whereClause)
	{
		String whereClauseFinal = "hrm_statement_ID=? ";
		if (whereClause != null)
			whereClauseFinal += whereClause;
		List<Mhrmstatementline> list = new Query(getCtx(), I_hrm_statement_line.Table_Name, whereClauseFinal, get_TrxName()).setOrderBy("Line")
										.setParameters(gethrm_statement_ID())
										.list();
		return list.toArray(new Mhrmstatementline[list.size()]);
	}	//	getLines

	/**
	 * 	Get Statement Lines
	 * 	@param requery
	 * 	@return lines
	 */
	public Mhrmstatementline[] getLines (boolean requery)
	{
		if (m_lines == null || m_lines.length == 0 || requery)
			m_lines = getLines(null);
		set_TrxName(m_lines, get_TrxName());
		return m_lines;
	}	//	getLines

	/**
	 * 	Get Statement line
	 * 	@return lines
	 */
	public Mhrmstatementline[] getLines()
	{
		return getLines(false);
	}	//	getLines
	
	private SalarySum getSalaryAmount(int paymentListID, int BPartnerID){
		
		SalarySum amt = new SalarySum();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = " select * from hrm_salarysum_v where hrm_payment_list_id = " + paymentListID +" \n " +
					 " and c_bpartner_id=" + BPartnerID;
		
		pstmt = null;
		rs = null;
		try{
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				amt.setSalary(rs.getBigDecimal("totalsum"));
				amt.setAmount(rs.getBigDecimal("amount"));
				amt.setVacation(rs.getBigDecimal("vacation"));
				amt.setAidVacation(rs.getBigDecimal("aidvacation"));
				amt.setMedicare(rs.getBigDecimal("medicare"));
				amt.setReview(rs.getBigDecimal("review"));
				amt.setBonus(rs.getBigDecimal("bonus"));
				amt.setMaid(rs.getBigDecimal("materialaid"));
				amt.setMaidOff(rs.getBigDecimal("materialaidtaxoff"));
				amt.setProfit(rs.getBigDecimal("profit"));
				amt.setOpv(rs.getBigDecimal("opv"));
				amt.setIpn(rs.getBigDecimal("ipn"));
				amt.setTotalOff(rs.getBigDecimal("totaloff"));
				amt.setIllness(rs.getBigDecimal("illness"));
				amt.setOvertime(rs.getBigDecimal("overtime"));
				amt.setBtrip(rs.getBigDecimal("btrip"));
				amt.setInter(rs.getBigDecimal("inter"));
				amt.setLoan(rs.getBigDecimal("loan"));
				amt.setCheckoff(rs.getBigDecimal("checkoff"));
			}
		}catch(SQLException e){
			log.log(Level.WARNING, e.getLocalizedMessage());
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		return amt;
	}
	
	public class SalarySum {
		
		BigDecimal salary = new BigDecimal(0);
		BigDecimal vacation = new BigDecimal(0);
		BigDecimal aidVacation = new BigDecimal(0);
		BigDecimal review = new BigDecimal(0);
		BigDecimal bonus = new BigDecimal(0);
		BigDecimal medicare = new BigDecimal(0);
		BigDecimal maid = new BigDecimal(0);
		BigDecimal maidOff = new BigDecimal(0);
		BigDecimal profit = new BigDecimal(0);
		BigDecimal totalOff = new BigDecimal(0);
		BigDecimal amount = new BigDecimal(0);
		BigDecimal illness = new BigDecimal(0);
		BigDecimal overtime = new BigDecimal(0);
		BigDecimal btrip = new BigDecimal(0);
		BigDecimal inter = new BigDecimal(0);
		BigDecimal loan = new BigDecimal(0);
		BigDecimal checkoff = new BigDecimal(0);
		
		// Tax
		BigDecimal opv = new BigDecimal(0);
		BigDecimal ipn = new BigDecimal(0);

		public BigDecimal getSalary() {
			return salary;
		}
		public void setSalary(BigDecimal salary) {
			this.salary = salary;
		}
		public BigDecimal getVacation() {
			return vacation;
		}
		public void setVacation(BigDecimal vacation) {
			this.vacation = vacation;
		}
		public BigDecimal getAidVacation() {
			return aidVacation;
		}
		public void setAidVacation(BigDecimal aidVacation) {
			this.aidVacation = aidVacation;
		}
		public BigDecimal getReview() {
			return review;
		}
		public void setReview(BigDecimal review) {
			this.review = review;
		}
		public BigDecimal getBonus() {
			return bonus;
		}
		public void setBonus(BigDecimal bonus) {
			this.bonus = bonus;
		}
		public BigDecimal getMedicare() {
			return medicare;
		}
		public void setMedicare(BigDecimal medicare) {
			this.medicare = medicare;
		}
		public BigDecimal getMaid() {
			return maid;
		}
		public void setMaid(BigDecimal maid) {
			this.maid = maid;
		}
		public BigDecimal getMaidOff() {
			return maidOff;
		}
		public void setMaidOff(BigDecimal maidOff) {
			this.maidOff = maidOff;
		}
		public BigDecimal getProfit() {
			return profit;
		}
		public void setProfit(BigDecimal profit) {
			this.profit = profit;
		}
		public BigDecimal getOpv() {
			return opv;
		}
		public void setOpv(BigDecimal opv) {
			this.opv = opv;
		}
		public BigDecimal getIpn() {
			return ipn;
		}
		public void setIpn(BigDecimal ipn) {
			this.ipn = ipn;
		}
		public BigDecimal getTotalOff() {
			return totalOff;
		}
		public void setTotalOff(BigDecimal totalOff) {
			this.totalOff = totalOff;
		}
		public BigDecimal getAmount() {
			return amount;
		}
		public void setAmount(BigDecimal amount) {
			this.amount = amount;
		}
		public BigDecimal getIllness() {
			return illness;
		}
		public void setIllness(BigDecimal illness) {
			this.illness = illness;
		}
		public BigDecimal getOvertime() {
			return overtime;
		}
		public void setOvertime(BigDecimal overtime) {
			this.overtime = overtime;
		}
		public BigDecimal getBtrip() {
			return btrip;
		}
		public void setBtrip(BigDecimal btrip) {
			this.btrip = btrip;
		}
		public BigDecimal getInter() {
			return inter;
		}
		public void setInter(BigDecimal inter) {
			this.inter = inter;
		}
		public BigDecimal getLoan() {
			return loan;
		}
		public void setLoan(BigDecimal loan) {
			this.loan = loan;
		}
		public BigDecimal getCheckoff() {
			return checkoff;
		}
		public void setCheckoff(BigDecimal checkoff) {
			this.checkoff = checkoff;
		}
		
	}
	
}
